package srdb.migration.dbhandler.srdb;

import java.sql.Array;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.Hashtable;
import java.util.Vector;

import org.srdbsql.jdbc2.AbstractJdbc2Connection;

import srdb.migration.MigrationToolkit;
import srdb.migration.Utility;
import srdb.migration.common.IMTKConnection;
import srdb.migration.common.MTKConstants;
import srdb.migration.common.MTKMetaData;
import srdb.migration.schema.metadata.Column;
import srdb.migration.schema.metadata.Constraint;
import srdb.migration.schema.metadata.DataType;
import srdb.migration.schema.metadata.Domain;
import srdb.migration.schema.metadata.Function;
import srdb.migration.schema.metadata.FunctionList;
import srdb.migration.schema.metadata.Grant;
import srdb.migration.schema.metadata.Group;
import srdb.migration.schema.metadata.Index;
import srdb.migration.schema.metadata.PackageList;
import srdb.migration.schema.metadata.ProcedureList;
import srdb.migration.schema.metadata.Role;
import srdb.migration.schema.metadata.Rule;
import srdb.migration.schema.metadata.Schema;
import srdb.migration.schema.metadata.Sequence;
import srdb.migration.schema.metadata.SequenceList;
import srdb.migration.schema.metadata.Table;
import srdb.migration.schema.metadata.TableList;
import srdb.migration.schema.metadata.Trigger;
import srdb.migration.schema.metadata.View;
import srdb.migration.schema.metadata.ViewList;

public class MetaData extends MTKMetaData {
	protected final String sqlIndexesDefs = "SELECT indexname, indexdef FROM sr_catalog.sr_indexes WHERE schemaname = ? AND tablename = ?";
	protected final String sqlConsts = "SELECT conname,contype FROM sr_catalog.sr_constraint a, sr_class b, sr_namespace c WHERE a.connamespace = c.oid AND a.conrelid = b.oid AND c.nspname = ? AND b.relname = ?";

	protected IMTKConnection conn = null;
	protected PreparedStatement psIndexesDefs = null;
	protected PreparedStatement psConsts = null;
	protected HashMap<String, ArrayList> mapTableIndexesDefs = new HashMap();
	PreparedStatement getDataTypeColumns = null;
	PreparedStatement getDataTypeLabels = null;

	PreparedStatement getTableInheritance = null;
	private ArrayList<String> tableList = new ArrayList();
	private int tableNameCounter = 1;
	private ArrayList<String> indexList = new ArrayList();
	private int indexNameCounter = 1;

	private String trigerFunctionsStr = "";
	private PreparedStatement getTriggerFunctions = null;

	private String getSequenceSQLStr = null;

	private String commaSepUserNames = null;

	private PreparedStatement getUserById = null;

	PreparedStatement domainConstraints = null;

	public MetaData(IMTKConnection conn) throws Exception {
		this.conn = conn;

		if (conn != null)
			this.dbConn = conn.getConnection();
	}

	public MetaData(IMTKConnection conn, String targetSchemaName)
			throws Exception {
		this.conn = conn;

		if (conn != null) {
			this.dbConn = conn.getConnection();
		}

		this.targetSchemaName = targetSchemaName;
	}

	protected void initSynonymStatement(String commaSepSynNames)
			throws SQLException {
	}

	protected void initAllPublicSynonymStatement() throws SQLException {
	}

	protected void initAllPrivateSynonymStatement() throws SQLException {
	}

	protected void initTableStatement(String commaSepTableNames)
			throws SQLException {
		this.getTablesStr = "SELECT a.relname table_name, relhasoids,  substring(array_to_string(a.reloptions, ',') from 'fillfactor=([0-9]*)') AS fillfactor, a.oid oid FROM sr_class a, sr_namespace b WHERE a.relnamespace = b.oid and b.nspname = ? ";

		if (commaSepTableNames != null) {
			this.getTablesStr = (this.getTablesStr + " AND a.relname IN ("
					+ commaSepTableNames + ") ");
		}

		this.getTablesStr += " AND a.relkind = 'r' ORDER BY a.oid ";
		this.getTables = this.dbConn.prepareStatement(this.getTablesStr);

		this.getTableInheritance = this.dbConn
				.prepareStatement("select c.relname tablename,n.nspname schemaname from sr_inherits i ,sr_class c, sr_namespace n where  i.inhparent = c.oid  and n.oid = c.relnamespace and i.inhrelid = ?");
	}

	protected void initColumnStatement() throws SQLException {
		this.getColumnsStr = "SELECT column_name, data_type, character_maximum_length as data_length, numeric_precision as data_precision, numeric_scale as data_scale, is_nullable as nullable, column_default as data_default, col_description(('\"' || table_schema || '\".\"' || table_name  || '\"')::sr_catalog.regclass, ordinal_position) as comments, sr_catalog.format_type(c.atttypid, c.atttypmod) as ex_data_type, c.attinhcount FROM information_schema.columns a, sr_class b, sr_attribute c, sr_namespace d WHERE table_schema =  ? AND table_name = ? AND a.table_name = b.relname AND b.relnamespace = d.oid AND d.nspname = a.table_schema AND b.oid = c.attrelid AND a.column_name = c.attname ORDER BY ordinal_position";

		this.getColumns = this.dbConn.prepareStatement(this.getColumnsStr);
	}

	protected void initTriggerStatement(String commaSepTableNames)
			throws SQLException {
		this.trigerFunctionsStr = "select pr.proname, pr.prosrc, lan.lanname,provolatile                      from sr_proc  pr                      JOIN sr_trigger tg ON tg.tgfoid = pr.oid                      JOIN sr_language lan on lan.oid = pr.prolang                       where tg.oid = ?";

		this.getTriggersStr = "SELECT trig.oid, trig.tgname, des.description, sr_get_userbyid(proc.proowner) as OWNER, trig.tgtype, trig.tgenabled, c.relname,sr_get_userbyid(c.relowner) as TABLE_OWNER FROM   sr_proc proc, sr_trigger trig        LEFT OUTER JOIN sr_class c on (trig.tgrelid = c.oid)        LEFT OUTER JOIN sr_description des on (des.objoid=trig.oid and des.objsubid=0)        JOIN sr_namespace ns ON ns.oid = c.relnamespace  ";

		if (getDBServerMajorVersion() >= 9)
			this.getTriggersStr += " WHERE  trig.tgisinternal = false ";
		else {
			this.getTriggersStr += " WHERE  trig.tgisconstraint = false ";
		}

		this.getTriggersStr += "AND proc.oid = trig.tgfoid AND proc.prolang IN (SELECT oid FROM sr_language WHERE upper(lanname)='PLPGSQL') AND ns.nspname = ? ";

		if ((commaSepTableNames != null)
				&& (commaSepTableNames.trim().length() > 0)) {
			this.getTriggersStr = (this.getTriggersStr + "AND c.relname in ("
					+ commaSepTableNames + ")");
		}

		this.getTriggersStr += " ORDER BY trig.oid";

		this.getTriggerFunctions = this.conn.getConnection().prepareStatement(
				this.trigerFunctionsStr);
		this.getTriggers = this.conn.getConnection().prepareStatement(
				this.getTriggersStr);
	}

	protected void initViewStatement(String commaSepViewNames)
			throws SQLException {
		this.getViewsStr = "select c.relname AS viewname, sr_get_viewdef(c.oid) AS definition from sr_class c LEFT JOIN sr_namespace n ON n.oid = c.relnamespace where c.relkind = 'v'::\"char\" and n.nspname=?";

		if ((commaSepViewNames != null)
				&& (commaSepViewNames.trim().length() > 0)) {
			this.getViewsStr = (this.getViewsStr + " AND c.relname IN ("
					+ commaSepViewNames + ")");
		}

		this.getViewsStr += " order by c.oid";

		this.getViews = this.conn.getConnection().prepareStatement(
				this.getViewsStr);
	}

	protected void initSequenceStatement(String commaSepSequenceNames)
			throws SQLException {
		this.getSequencesStr = "select sequence_name from information_schema.sequences s, sr_class c, sr_namespace n where sequence_schema=n.nspname and s.sequence_name = c.relname and c.relnamespace = n.oid and n.nspname = ? and c.oid NOT IN    (select d.objid from sr_depend d, sr_class c, sr_class cobj    where c.oid=d.refobjid and cobj.oid=d.objid and  n.oid = c.relnamespace AND c.relkind='r' and cobj.relkind = 'S' )  ";

		if ((commaSepSequenceNames != null)
				&& (commaSepSequenceNames.trim().length() > 0)) {
			this.getSequencesStr = (this.getSequencesStr
					+ " AND sequence_name IN (" + commaSepSequenceNames + ")");
		}

		this.getSequenceSQLStr = "SELECT last_value, min_value, max_value, cache_value, is_cycled, increment_by  FROM ";

		this.getSequences = this.conn.getConnection().prepareStatement(
				this.getSequencesStr);
	}

	protected void initProcStatement(String commaSepProcNames)
			throws SQLException {
		this.getProcsStr = "select  nspname,proname, pronargs,proargtypes,proargmodes,proargnames,prosrc,typ.typname as rettypename, lan.lanname,proargmodes, proallargtypes, proretset,provolatile from sr_proc proc JOIN sr_language lan on lan.oid = proc.prolang   JOIN sr_type typ on typ.oid = proc.prorettype   JOIN sr_namespace nsp on nsp.oid=pronamespace   WHERE nspname = ?   and typ.typname <> 'trigger' and lan.lanname not in ('internal') ";

		if ((commaSepProcNames != null)
				&& (commaSepProcNames.trim().length() > 0)) {
			this.getProcsStr = (this.getProcsStr + "AND proname IN ("
					+ commaSepProcNames + ")");
		}

		this.getProcsStr += " ORDER BY proc.oid";

		this.getProcs = this.conn.getConnection().prepareStatement(
				this.getProcsStr);
	}

	protected void initPackageStatement(String commaSepPackageNames)
			throws SQLException {
	}

	protected void initConstraintStatement(String commaSepTableNames)
			throws SQLException {
		this.getConstraintsStr = "SELECT a.conname as constraint_name, a.contype as constraint_type, b.relname as table_name, a.consrc as search_condition, '' as r_constraint_name,'' as r_owner, confdeltype as delete_rule,  substring(array_to_string(b.reloptions, ',') from 'fillfactor=([0-9]*)') AS fillfactor, confupdtype as update_rule, a.confmatchtype, '' as r_table_name FROM sr_constraint a, sr_class b, sr_namespace c WHERE a.connamespace = c.oid AND a.conrelid = b.oid AND contype IN ('p','u','c') AND c.nspname = ? ";

		if (commaSepTableNames != null) {
			this.getConstraintsStr = (this.getConstraintsStr
					+ " AND b.relname IN (" + commaSepTableNames + ") ");
		}

		this.getConstraintsStr = (this.getConstraintsStr
				+ " UNION SELECT a.conname as constraint_name, a.contype as constraint_type, "
				+ "b.relname as table_name, a.consrc as search_condition, d.conname as r_constraint_name, f.nspname as r_owner,"
				+ "a.confdeltype as delete_rule,substring(array_to_string(b.reloptions, ',') from 'fillfactor=([0-9]*)') AS fillfactor, a.confupdtype as update_rule, a.confmatchtype, e.relname as r_table_name "
				+ "FROM sr_constraint a, sr_class b, sr_namespace c, sr_constraint d, sr_class e, sr_namespace f "
				+ "WHERE a.connamespace = c.oid AND a.conrelid = b.oid AND a.contype = 'f' " + "AND a.confrelid = d.conrelid AND d.contype = 'p' AND a.confrelid = e.oid AND e.relnamespace = f.oid AND c.nspname = ? ");

		if (commaSepTableNames != null) {
			this.getConstraintsStr = (this.getConstraintsStr
					+ " AND b.relname IN (" + commaSepTableNames + ") ");
		}

		this.getConstraintColumnsStr = "SELECT column_name, ordinal_position as \"position\", table_name FROM information_schema.key_column_usage WHERE table_schema = ? and constraint_name = ? AND table_name = ? ";

		this.getConstraints = this.conn.getConnection().prepareStatement(
				this.getConstraintsStr);
		this.getConstraintColumns = this.conn.getConnection().prepareStatement(
				this.getConstraintColumnsStr);
	}

	protected void initIndexeStatement(String commaSepTableNames)
			throws SQLException {
		this.getIndexesStr = "SELECT b.relname as index_name, c.relname as table_name, indisunique as uniqueness,b.oid as oid,c.relfilenode FROM sr_index a, sr_class b, sr_class c, sr_namespace d WHERE a.indisprimary = false AND a.indexrelid = b.oid AND a.indrelid = c.oid AND b.relnamespace = d.oid AND d.nspname = ? ";

		this.getIndexColumnsStr = "SELECT e.attname as column_name, e.attnum as column_position, c.relname as table_name,a.indexprs as expression FROM sr_index a, sr_class b, sr_class c, sr_namespace d, sr_attribute e WHERE a.indexrelid = b.oid AND a.indrelid = c.oid AND b.relnamespace = d.oid AND d.nspname = ? AND a.indexrelid = e.attrelid AND b.relname = ? ORDER BY column_position ";

		if (commaSepTableNames != null) {
			this.getIndexesStr = (this.getIndexesStr + " AND c.relname IN ("
					+ commaSepTableNames + ") ");
		}

		this.getIndexesStr += " AND b.relname NOT IN (SELECT e.conname FROM sr_catalog.sr_constraint e, sr_class f, sr_namespace g  WHERE e.connamespace = g.oid AND e.conrelid = f.oid AND g.nspname = d.nspname AND f.relname = c.relname and e.contype = 'u')";

		this.getIndexes = this.conn.getConnection().prepareStatement(
				this.getIndexesStr);

		if ((MigrationToolkit.getInstance().getTargetDBType() != MTKConstants.DATABASE_TYPE.SRDB)
				&& (MigrationToolkit.getInstance().getTargetDBType() != MTKConstants.DATABASE_TYPE.ENTERPRISEDB))
			this.getIndexColumns = this.conn.getConnection().prepareStatement(
					this.getIndexColumnsStr);
	}

	protected Table getTable(String TableName) throws SQLException {
		ResultSet rs = null;
		Table table = null;
		try {
			if (this.getColumns == null) {
				initColumnStatement();
			}

			this.getColumns.setString(1, this.schema.getName());
			this.getColumns.setString(2, TableName);
			rs = this.getColumns.executeQuery();
			boolean colCaseSensitive = false;
			boolean tabCaseSensitive = false;
			boolean isSimpleTableName = Utility.isValidEDBIdentifier(TableName);

			if (MigrationToolkit.getInstance().getTargetDBType() == MTKConstants.DATABASE_TYPE.ORACLE) {
				if (TableName.length() > 30) {
					String tempTableName = TableName.substring(0, 26);
					tempTableName = tempTableName.concat("t");
					if (this.tableList.contains(tempTableName)) {
						tempTableName = tempTableName.concat(""
								+ this.tableNameCounter++);
					}
					TableName = tempTableName;
					this.tableList.add(tempTableName);
				} else {
					this.tableList.add(TableName);
				}

			}

			if (((tabCaseSensitive = isCaseSensitive(TableName))
					|| (!isSimpleTableName) || (TableName.startsWith("_")) ? 1
						: 0) != 0) {
				TableName = "\"" + TableName + "\"";
			}

			while (rs.next()) {
				if (table == null) {
					table = new Table(this.schema, TableName,
							this.targetSchemaName);
				}

				String colName = rs.getString("column_name");

				int inhCount = rs.getInt("attinhcount");

				boolean isSimpleColumnName = Utility
						.isValidEDBIdentifier(colName);

				if (((colCaseSensitive = isCaseSensitive(colName))
						|| (!isSimpleColumnName) || (colName.startsWith("_")) ? 1
							: 0) != 0) {
					colName = "\"" + colName + "\"";
				}

				Column column = new Column(table, colName);
				String dataType = "";
				String exDataType = "";
				String[] typeNameStr = null;

				if (((MigrationToolkit.getInstance().getSourceDBType() == MTKConstants.DATABASE_TYPE.ENTERPRISEDB) || (MigrationToolkit
						.getInstance().getSourceDBType() == MTKConstants.DATABASE_TYPE.SRDB))
						&& (MigrationToolkit.getInstance().getTargetDBType() == MTKConstants.DATABASE_TYPE.SQLSERVER)) {
					if (Utility.isSQLServerVersion2005()) {
						typeNameStr = toSQLServer2005Type(
								rs.getString("data_type"),
								rs.getString("ex_data_type"));
						dataType = typeNameStr[0];
						exDataType = typeNameStr[1];
					} else {
						dataType = rs.getString("data_type");
						exDataType = rs.getString("ex_data_type");
					}
				} else {
					dataType = rs.getString("data_type");
					exDataType = rs.getString("ex_data_type");
				}

				int dataLength = calculatePGDataLength(dataType,
						rs.getInt("data_length"), rs.getInt("data_precision"),
						rs.getInt("data_scale"));

				column.addDetail(dataType, dataLength,
						rs.getInt("data_precision"), rs.getInt("data_scale"),
						rs.getString("nullable"), colCaseSensitive,
						rs.getString("data_default"), rs.getString("comments"),
						exDataType);

				column.setInhCount(inhCount);
				table.addColumn(column);
			}
		} catch (SQLException se) {
			throw se;
		} finally {
			closeResultSet(rs);
		}

		return table;
	}

	private String[] toSQLServer2005Type(String typeName, String exDataType) {
		String[] typeNameStr = { null, null };

		if ((typeName.equalsIgnoreCase("timestamp with time zone"))
				|| (typeName.equalsIgnoreCase("time without time zone"))
				|| (typeName.equalsIgnoreCase("time with time zone"))) {
			typeNameStr[0] = "TIMESTAMP";
			typeNameStr[1] = "TIMESTAMP";
		} else {
			typeNameStr[0] = typeName;
			typeNameStr[1] = exDataType;
		}
		return typeNameStr;
	}

	public void getTables(String commaSepTableNames, boolean importViewAsTable)
			throws SQLException {
		super.getTables(commaSepTableNames, importViewAsTable);

		if (importViewAsTable) {
			initViewStatement(commaSepTableNames);
			getViewsAsTables();
		}
	}

	private void getViewsAsTables() throws SQLException {
		writeLog("Getting View Definitions...");
		ResultSet rs = null;
		try {
			this.getViews.setString(1, this.schema.getName());
			rs = this.getViews.executeQuery();

			while (rs.next()) {
				Table table = getTable(rs.getString(1));
				if (table != null)
					this.schema.addTable(table);
			}
		} finally {
			if (rs != null)
				rs.close();
		}
	}

	private int calculatePGDataLength(String dataType, int dataLength,
			int dataPrecision, int dataScale) {
		if (dataType.equalsIgnoreCase("integer")) {
			return 8;
		}
		if (dataType.equalsIgnoreCase("numeric"))
			return dataPrecision;
		if (dataType.startsWith("timestamp")) {
			return 8;
		}

		return dataLength;
	}

	protected void getTables() throws SQLException {
		writeLog("Getting Table Definitions");
		ResultSet rs = null;
		ResultSet rsInh = null;
		try {
			this.getTables.setString(1, this.schema.getName());
			rs = this.getTables.executeQuery();

			while (rs.next()) {
				Table table = getTable(rs.getString("table_name"));
				String oid = rs.getString("oid");
				table.setOid(oid);
				if (table != null) {
					table.setRelHasOIDs(rs.getBoolean("relhasoids"));
					table.setFillFactor(rs.getString("fillfactor"));
					this.getTableInheritance.setInt(1, Integer.parseInt(oid));
					rsInh = this.getTableInheritance.executeQuery();
					while (rsInh.next()) {
						table.setIsInherited(true);
						Table parentTable = new Table(new Schema(
								rsInh.getString("schemaname"), true),
								rsInh.getString("tablename"), null);
						table.getInheritedFromList().add(parentTable);
					}
					this.schema.addTable(table);
				}
			}
		} catch (SQLException se) {
			throw se;
		} finally {
			closeResultSet(rs);
			closeResultSet(rsInh);
		}
	}

	protected void getProcedures() throws SQLException {
		throw new UnsupportedOperationException(
				"Migration of Procedures from Postgres not supported.");
	}

	protected void getFunctions() throws SQLException {
		this.getProcs.setString(1, this.schema.getName());
		ResultSet rs = null;
		PreparedStatement typeStmt = null;
		try {
			rs = this.getProcs.executeQuery();
			typeStmt = this.conn
					.getConnection()
					.prepareStatement(
							"SELECT sr_catalog.format_type(sr_type.oid, typtypmod) AS type_name      FROM sr_catalog.sr_type      JOIN sr_catalog.sr_namespace ON (sr_namespace.oid = typnamespace)      WHERE sr_type.oid = ?");

			while (rs.next()) {
				String funcSql = "CREATE OR REPLACE FUNCTION ";
				funcSql = funcSql + rs.getString(2);
				String returnType = rs.getString(8);
				if (rs.getBoolean("proretset")) {
					returnType = "SETOF " + returnType;
				}
				String[] argnames = new String[0];

				if (rs.getArray("proargnames") != null) {
					argnames = (String[]) (String[]) rs.getArray("proargnames")
							.getArray();
				}

				String proArgTypesStr = rs.getString("proargtypes");
				String[] argTypes = (proArgTypesStr == null)
						|| (proArgTypesStr.trim().length() == 0) ? new String[0]
						: proArgTypesStr.split("\\s+");

				if (rs.getString("proallargtypes") != null) {
					Long[] array = (Long[]) (Long[]) rs.getArray(
							"proallargtypes").getArray();
					argTypes = new String[array.length];
					for (int i = 0; i < argTypes.length; i++) {
						argTypes[i] = array[i].toString();
					}
				}

				Array modesArray = rs.getArray("proargmodes");
				String[] modes = null;
				if (modesArray != null) {
					modes = (String[]) (String[]) modesArray.getArray();
				}
				if (modes == null) {
					modes = new String[0];
				}
				int size = argTypes.length;

				if (size > 0) {
					funcSql = funcSql + "(";
					for (int i = 0; i < argTypes.length; i++) {
						if (i > 0) {
							funcSql = funcSql + ", ";
						}
						typeStmt.setInt(1, Integer.parseInt(argTypes[i]));
						ResultSet typeRs = typeStmt.executeQuery();
						String type = "";
						if (typeRs.next())
							type = typeRs.getString(1);
						else {
							throw new SQLException(
									"Unable to identify argument types");
						}
						typeRs.close();
						String mode = "";
						if (modes.length > i) {
							switch (modes[i].charAt(0)) {
							case 'i':
								mode = "IN";
								break;
							case 'o':
								mode = "OUT";
								break;
							case 'b':
								mode = "INOUT";
							}

						}

						if ((argnames.length > i) && (argnames[i] != null)) {
							funcSql = funcSql + argnames[i] + " ";
						}

						funcSql = funcSql + mode + " " + type;
					}
					funcSql = funcSql + ")";
				} else {
					funcSql = funcSql + "()";
				}

				funcSql = funcSql + "\nRETURNS " + returnType + " AS $$ "
						+ rs.getString("prosrc") + " $$ LANGUAGE '"
						+ rs.getString("lanname") + "' ";

				String volatileAttribute = rs.getString("provolatile");

				if (volatileAttribute.equalsIgnoreCase("i"))
					volatileAttribute = "immutable";
				else if (volatileAttribute.equalsIgnoreCase("s"))
					volatileAttribute = "stable";
				else {
					volatileAttribute = "volatile";
				}

				funcSql = funcSql + volatileAttribute + " ";

				Function f = new Function(rs.getString("proname"),
						this.schema.getName());
				f.setSQL(funcSql);
				this.schema.addFunction(f);
			}
		} finally {
			closeResultSet(rs);

			if (typeStmt != null)
				typeStmt.close();
		}
	}

	protected void getViews() throws SQLException {
		this.getViews.setString(1, this.schema.getName());
		ResultSet rs = this.getViews.executeQuery();
		try {
			while (rs.next()) {
				View v = new View(rs.getString(1), this.schema.getName());
				v.setSQL(rs.getString(2));
				this.schema.addView(v);
			}
		} finally {
			closeResultSet(rs);
		}
	}

	protected void getPublicSynonyms() throws SQLException {
		throw new UnsupportedOperationException(
				"Public Synonyms are not supported in Postgres.");
	}

	protected void getAllPublicSynonyms() throws SQLException {
		throw new UnsupportedOperationException(
				"Public Synonyms are not supported in Postgres.");
	}

	protected void getAllPrivateSynonyms() throws SQLException {
		throw new UnsupportedOperationException(
				"Public Synonyms are not supported in Postgres.");
	}

	protected void getTriggers() throws SQLException {
		this.getTriggers.setString(1, this.schema.getName());
		this.getTriggers.execute();
		ResultSet rs = null;
		ResultSet funcRs = null;
		try {
			rs = this.getTriggers.getResultSet();
			while (rs.next()) {
				this.getTriggerFunctions.setInt(1, rs.getInt(1));
				funcRs = this.getTriggerFunctions.executeQuery();
				String proname = "";
				Function f = null;
				if (funcRs.next()) {
					f = new Function(funcRs.getString(1), this.schema.getName());
					proname = f.getName();

					f.setSQL("CREATE OR REPLACE FUNCTION "
							+ funcRs.getString(1)
							+ "() returns trigger as $$\n"
							+ funcRs.getString(2) + "$$\n language 'plpgsql'");
				}

				funcRs.close();
				Trigger tr = new Trigger(rs.getString("tgname"));
				String[] conditions = getTriggerCondition(rs.getInt("tgtype"));
				String sql = "CREATE TRIGGER "
						+ tr.getName()
						+ "\n"
						+ conditions[0]
						+ " "
						+ conditions[1]
						+ "\n"
						+ "ON "
						+ this.schema.getName()
						+ "."
						+ Utility.escape(rs.getString("relname"),
								MTKConstants.DATABASE_TYPE.SRDB)
						+ conditions[2] + " \n EXECUTE PROCEDURE " + proname
						+ "();";

				if (f != null) {
					sql = f.getSQL() + ";" + sql;
				}
				tr.setBody(sql);
				tr.setOwnerName(rs.getString("OWNER"));
				tr.setTableOwnerName(rs.getString("TABLE_OWNER"));

				this.schema.addTrigger(tr);
			}
		} finally {
			closeResultSet(funcRs);
			closeResultSet(rs);
		}
	}

	protected String[] getTriggerCondition(int triggerCond) {
		String[] triggerCondition = new String[3];
		String whenString = "";
		String eventsString = "";
		String forEachStringPlpgsql = "";
		int whenCond = (triggerCond & 0x2) >> 1;
		int insCond = (triggerCond & 0x4) >> 2;
		int delCond = (triggerCond & 0x8) >> 3;
		int updCond = (triggerCond & 0x10) >> 4;
		int forEachCond = triggerCond & 0x1;

		if (whenCond == 1)
			whenString = "BEFORE";
		else {
			whenString = "AFTER";
		}

		if (insCond == 1) {
			eventsString = "INSERT";
		}
		if (delCond == 1) {
			if (!eventsString.equals("")) {
				eventsString = eventsString + " OR ";
			}
			eventsString = eventsString + "DELETE";
		}
		if (updCond == 1) {
			if (!eventsString.equals("")) {
				eventsString = eventsString + " OR ";
			}
			eventsString = eventsString + "UPDATE";
		}

		if (forEachCond == 1)
			forEachStringPlpgsql = "\nFOR EACH ROW";
		else {
			forEachStringPlpgsql = "\nFOR EACH STATEMENT";
		}

		triggerCondition[0] = whenString;
		triggerCondition[1] = eventsString;
		triggerCondition[2] = forEachStringPlpgsql;
		return triggerCondition;
	}

	protected void getSequences() throws SQLException {
		this.getSequences.setString(1, this.schema.getName());
		Statement stm = null;
		ResultSet rs = this.getSequences.executeQuery();
		ResultSet sqlRs = null;
		try {
			stm = this.conn.getConnection().createStatement();
			while (rs.next()) {
				String seqName = rs.getString(1);
				sqlRs = stm.executeQuery(this.getSequenceSQLStr
						+ this.schema.getName() + "." + seqName);
				while (sqlRs.next()) {
					Sequence s = new Sequence(seqName, this.schema.getName(),
							false);
					s.setCacheSize(sqlRs.getInt("cache_value"));
					s.setCycleFlag(sqlRs.getBoolean("is_cycled") ? "Y" : "");
					s.setIncrement(sqlRs.getString("increment_by"));
					s.setLastNumber(sqlRs.getString("last_value"));
					s.setRange(sqlRs.getString("min_value"),
							sqlRs.getString("max_value"));
					this.schema.addSequence(s);
				}
				sqlRs.close();
				stm.clearWarnings();
			}
		} finally {
			closeResultSet(rs);
			closeResultSet(sqlRs);

			if (stm != null)
				stm.close();
		}
	}

	protected void getPackages() throws SQLException {
		throw new UnsupportedOperationException(
				"Packages are not supported in Postgres.");
	}

	protected void getConstraints() throws SQLException {
		this.getConstraints.setString(1, this.schema.getName());
		this.getConstraints.setString(2, this.schema.getName());
		super.getConstraints();
	}

	protected void setConstraintColumnsPSParams(Constraint constraint)
			throws SQLException {
		this.getConstraintColumns.setString(1, this.schema.getName());
		this.getConstraintColumns.setString(2, constraint.getName());

		this.getConstraintColumns.setString(3,
				Utility.removeEnclosingQuotes(constraint.getTableName()));
	}

	protected void setFKConstraintPSParams(Constraint constraint,
			String refConstName) throws SQLException {
		this.getConstraintColumns
				.setString(1, constraint.getRefIntSchemaName());
		this.getConstraintColumns.setString(2, refConstName);
		this.getConstraintColumns.setString(3, Utility
				.removeEnclosingQuotes(constraint.getRefIntegrityTable()));
	}

	protected void getIndexes() throws SQLException {
		writeLog("Getting Index Definitions");
		ResultSet rs = null;
		ResultSet irs = null;
		ResultSet sqlRs = null;
		Statement stmt = null;
		try {
			this.getIndexes.setString(1, this.schema.getName());
			rs = this.getIndexes.executeQuery();

			while (rs.next()) {
				String indexName = rs.getString("index_name");
				String originalIndexName = indexName;
				if (MigrationToolkit.getInstance().getTargetDBType() == MTKConstants.DATABASE_TYPE.ORACLE) {
					if (indexName.length() > 30) {
						String tempIndexName = indexName.substring(0, 26);
						tempIndexName = tempIndexName.concat("i");
						if (this.indexList.contains(tempIndexName)) {
							tempIndexName = tempIndexName.concat(""
									+ this.indexNameCounter++);
						}
						indexName = tempIndexName;
						this.indexList.add(tempIndexName);
					} else {
						this.indexList.add(indexName);
					}
				}
				Table table = getTable(rs.getString("table_name"));
				Index index = new Index(table, indexName);

				String tableoid = rs.getString("relfilenode");
				String indexUniqueness = rs.getBoolean("uniqueness") ? "UNIQUE"
						: "NONUNIQUE";
				index.setUniqueness(indexUniqueness);

				stmt = this.dbConn.createStatement();
				sqlRs = stmt.executeQuery("select sr_get_indexdef("
						+ rs.getString("oid") + ")");
				if (sqlRs.next()) {
					index.setSql(sqlRs.getString(1));
				}
				sqlRs.close();
				stmt.close();
				if (originalIndexName.compareToIgnoreCase(indexName) != 0) {
					index.setSql(index.getSql().replaceFirst(originalIndexName,
							indexName));
				}

				if ((MigrationToolkit.getInstance().getTargetDBType() != MTKConstants.DATABASE_TYPE.SRDB)
						&& (MigrationToolkit.getInstance().getTargetDBType() != MTKConstants.DATABASE_TYPE.ENTERPRISEDB)) {
					this.getIndexColumns.setString(1, this.schema.getName());
					this.getIndexColumns.setString(2, originalIndexName);
					irs = this.getIndexColumns.executeQuery();
					while (irs.next()) {
						String columnName = irs.getString("column_name");
						String expression = irs.getString("expression");
						if ((columnName.compareToIgnoreCase("sr_expression_1") == 0)
								|| ((expression != null) && (expression.trim()
										.length() > 0))) {
							String sql = "select sr_get_expr('" + expression
									+ "'," + tableoid + ")";
							Statement estmt = this.conn.getConnection()
									.createStatement();
							ResultSet ers = estmt.executeQuery(sql);
							if (ers.next()) {
								columnName = ers.getString(1);
								index.setExpression(columnName);
							}
							ers.close();
							estmt.close();
						}

						index.addColumn(new Column(columnName));
					}

					irs.close();
				}

				if (index != null)
					this.schema.addIndex(index);
			}
		} catch (SQLException se) {
			throw se;
		} finally {
			if (irs != null) {
				irs.close();
			}

			if (rs != null) {
				rs.close();
			}
			if (sqlRs != null) {
				sqlRs.close();
			}
			if (stmt != null)
				stmt.close();
		}
	}

	public boolean supportsSynonyms() {
		String sql = "select count(*) from sr_tables where tablename = 'sr_synonym'";
		ResultSet rs = null;
		Statement stmt = null;
		int i;
		try {
			stmt = this.conn.getConnection().createStatement();
			rs = stmt.executeQuery(sql);
			rs.next();
			i = rs.getInt(1);
			if (i > 0) {
				// i = 1; jsr 33;
				return true;
			}
		} catch (Exception e) {

			e.printStackTrace();
		} finally {
			closeResultSet(rs);
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException sqle) {
			}
		}
		return false;
	}

	public String getVersion() throws SQLException {
		String sql = "select version()";
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = this.conn.getConnection().createStatement();
			rs = stmt.executeQuery(sql);
			rs.next();
			return rs.getString(1);
		} finally {
			closeResultSet(rs);
			try {
				if (stmt != null) {
					stmt.close();
				}
			} catch (SQLException sqle) {
			}
		}
	}

	public int getDBServerMajorVersion() throws SQLException {
		return ((AbstractJdbc2Connection) this.conn.getConnection())
				.getServerMajorVersion();
	}

	public int getDBServerMinorVersion() throws SQLException {
		return ((AbstractJdbc2Connection) this.conn.getConnection())
				.getServerMinorVersion();
	}

	public boolean isCaseSensitive(String identifierName) {
		return (identifierName != null)
				&& ((!identifierName.equals(identifierName.toLowerCase())) || (!Utility
						.isValidEDBIdentifier(identifierName)));
	}

	public void restoreTableIndexesDefs(Table table) throws SQLException {
		ArrayList<String> listTableIndexesDefs = (ArrayList) this.mapTableIndexesDefs
				.get(table.getTargetSchemaQualifiedName());
		if (listTableIndexesDefs != null) {
			for (String tableIndexesDefs : listTableIndexesDefs) {
				executeScript(tableIndexesDefs);
			}
		}
	}

	protected ArrayList<String> dropTableIndexesDefs(Table table,
			boolean createBackup) throws SQLException {
		ResultSet rs = null;

		if (createBackup) {
			this.listTableIndexesDefs.clear();
		}

		try {
			if (this.psIndexesDefs == null)
				this.psIndexesDefs = this.conn
						.getConnection()
						.prepareStatement(
								"SELECT indexname, indexdef FROM sr_catalog.sr_indexes WHERE schemaname = ? AND tablename = ?");
			else {
				this.psIndexesDefs.clearParameters();
			}

			this.psIndexesDefs.setString(1,
					Utility.removeEnclosingQuotes(table.getTargetSchemaName()));
			this.psIndexesDefs
					.setString(2, Utility.removeEnclosingQuotes(table
							.getEDBCompatibleName()));
			rs = this.psIndexesDefs.executeQuery();

			String tempTargetSchemaName = table.getTargetSchemaName();
			if (isCaseSensitive(tempTargetSchemaName)) {
				tempTargetSchemaName = "\"" + tempTargetSchemaName + "\"";
			}
			String indexName;
			while (rs.next()) {
				indexName = rs.getString(1);

				if (createBackup) {
					this.listTableIndexesDefs.add(rs.getString(2));
				}

				if (isCaseSensitive(indexName)) {
					indexName = "\"" + indexName + "\"";
				}

				executeScript("DROP INDEX " + tempTargetSchemaName + "."
						+ indexName + " CASCADE");
			}

			if ((createBackup) && (this.listTableIndexesDefs.size() > 0)) {
				this.mapTableIndexesDefs.put(
						table.getTargetSchemaQualifiedName(),
						this.listTableIndexesDefs);
			}

			return this.listTableIndexesDefs;
		} finally {
			String indexName;
			if (rs != null)
				rs.close();
		}
	}

	protected void dropTableConsts(MTKConstants.DATABASE_TYPE sourceDBType,
			Table table) throws SQLException {
		ResultSet rs = null;
		try {
			if (this.psConsts == null)
				this.psConsts = this.conn
						.getConnection()
						.prepareStatement(
								"SELECT conname,contype FROM sr_catalog.sr_constraint a, sr_class b, sr_namespace c WHERE a.connamespace = c.oid AND a.conrelid = b.oid AND c.nspname = ? AND b.relname = ?");
			else {
				this.psConsts.clearParameters();
			}

			this.psConsts.setString(1,
					Utility.removeEnclosingQuotes(table.getTargetSchemaName()));
			this.psConsts
					.setString(2, Utility.removeEnclosingQuotes(table
							.getEDBCompatibleName()));
			rs = this.psConsts.executeQuery();

			while (rs.next()) {
				String constName = rs.getString(1);

				if (isCaseSensitive(constName)) {
					constName = "\"" + constName + "\"";
				}
				if (rs.getString(2).equalsIgnoreCase("C")) {
					table.setHasCheckConst(true);
				}

				executeScript("ALTER TABLE "
						+ table.getTargetSchemaFullyQualifiedName(sourceDBType,
								MTKConstants.DATABASE_TYPE.ENTERPRISEDB, false)
						+ " DROP CONSTRAINT " + constName + " CASCADE ");
			}
		} finally {
			if (rs != null)
				rs.close();
		}
	}

	// public void dropTableConstsAndIndexes(
	// MTKConstants.DATABASE_TYPE sourceDBType, Table table)
	// throws SQLException {
	// dropTableConsts(sourceDBType, table);
	//
	// dropTableIndexesDefs(table,
	// sourceDBType == MTKConstants.DATABASE_TYPE.SQLSERVER);
	// }

	public String[] getSchemas() throws SQLException {
		String excludes = "'sr_catalog','information_schema'";

		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = this.conn.getConnection().createStatement();
			rs = stmt
					.executeQuery("select nspname from sr_namespace where nspname not in ("
							+ excludes
							+ ") and NOT nspname like 'sr__%' ESCAPE '_' order by oid");

			ArrayList list = new ArrayList();

			while (rs.next()) {
				list.add(rs.getString(1));
			}

			String[] arrayOfString = (String[]) list.toArray(new String[0]);
			return arrayOfString;
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}

				if (stmt != null)
					stmt.close();
			} catch (Exception e) {
			}
		}
	}

	protected void initDBLinkStatement() throws SQLException {
		throw new UnsupportedOperationException("Not supported yet.");
	}

	protected void getDBLinks() throws SQLException {
		throw new UnsupportedOperationException("Not supported yet.");
	}

	protected void getRoles() throws SQLException {
		ResultSet rs = this.getRoles.executeQuery();
		try {
			while (rs.next()) {
				String roleName = rs.getString(1);
				boolean isSuper = rs.getBoolean(2);
				boolean inherit = rs.getBoolean(3);
				boolean createRole = rs.getBoolean(4);
				boolean createDB = rs.getBoolean(5);
				boolean canUpdate = rs.getBoolean(6);
				boolean canLogin = rs.getBoolean(7);
				int connLimit = rs.getInt(8);
				String password = rs.getString(9);
				Timestamp validity = rs.getTimestamp(10);

				String SQL = "CREATE ROLE " + roleName + " "
						+ (canLogin ? "LOGIN" : "NOLOGIN") + " "
						+ (isSuper ? "SUPERUSER" : "NOSUPERUSER") + " "
						+ (inherit ? "INHERIT" : "NOINHERIT") + " "
						+ (createDB ? "CREATEDB" : "NOCREATEDB") + " "
						+ (createRole ? "CREATEROLE" : "NOCREATEROLE");

				Role r = new Role();
				r.setDba(isSuper);
				r.setRoleName(roleName);
				r.setSQL(SQL);
				this.schema.addRole(r);
			}
		} finally {
			closeResultSet(rs);
		}
	}

	protected void getUsers() throws SQLException {
	}

	protected void initRoleStatement(String commaSepRoleNames)
			throws SQLException {
		this.getRolesStr = "select rolname,rolsuper,rolinherit,rolcreaterole,rolcreatedb,rolcatupdate,rolcanlogin,rolconnlimit,rolpassword,rolvaliduntil,rolconfig,oid from sr_roles where rolname NOT IN (select groname from sr_group) ";

		if ((commaSepRoleNames != null)
				&& (commaSepRoleNames.trim().length() > 0)) {
			this.getRolesStr = (this.getRolesStr + " AND rolename in ("
					+ commaSepRoleNames + ")");
		}
		this.getRoles = this.conn.getConnection().prepareStatement(
				this.getRolesStr);
	}

	protected void initUserStatement(String commaSepUserNames)
			throws SQLException {
		this.getUsersStr = "SELECT usename, usesysid FROM sr_user";
		if ((commaSepUserNames != null)
				&& (commaSepUserNames.trim().length() > 0)) {
			this.getUsersStr = (this.getUsersStr + " AND usename IN ("
					+ commaSepUserNames + ")");
		}
		this.getUsers = this.conn.getConnection().prepareStatement(
				this.getUsersStr);
	}

	protected void initGrantStatement(String commaSepUserNames)
			throws SQLException {
		this.commaSepUserNames = commaSepUserNames;
	}

	protected void getGrants() throws SQLException {
		getDatabaseGrants();

		getSchemaGrants();

		getTableGrants();

		getFunctionGrants();

		getProcedureGrants();

		getPackagesGrants();

		getViewsGrants();

		getSequenceGrants();
	}

	private void getDatabaseGrants() throws SQLException {
		String sql = "SELECT d.datacl, u.usename, datname as objname from sr_database d, sr_user u  WHERE datname=current_database() and u.usesysid=d.datdba AND d.datacl IS NOT NULL ";

		if ((this.commaSepUserNames != null)
				&& (this.commaSepUserNames.trim().length() > 0)) {
			sql = sql + " AND u.usename IN (" + this.commaSepUserNames + ")";
		}

		getGenericGrants(sql, "DATABASE");
	}

	private void getSchemaGrants() throws SQLException {
		String sql = "SELECT n.nspacl, u.usename,'' as objname from sr_namespace n, sr_user u    WHERE nspname='"
				+ this.schema.getName() + "' AND " + "   u.usesysid=n.nspowner";

		if ((this.commaSepUserNames != null)
				&& (this.commaSepUserNames.trim().length() > 0)) {
			sql = sql + " AND u.usename IN (" + this.commaSepUserNames + ")";
		}

		getGenericGrants(sql, "SCHEMA");
	}

	private void getTableGrants() throws SQLException {
		String tableNames = "";
		TableList list = this.schema.getTables();
		if (list.size() == 0) {
			return;
		}
		for (int i = 0; i < list.size(); i++) {
			if (i > 0) {
				tableNames = tableNames + ",";
			}
			tableNames = tableNames + "'" + list.get(i).getName() + "'";
		}

		String sql = "SELECT c.relacl, u.usename, c.relname, n.nspname FROM sr_catalog.sr_namespace n, sr_catalog.sr_class c, sr_catalog.sr_user u  WHERE c.relnamespace = n.oid  AND u.usesysid = c.relowner AND c.relkind = 'r'  AND n.nspname='"
				+ this.schema.getName()
				+ "' AND c.relname IN ("
				+ tableNames
				+ ")";

		if ((this.commaSepUserNames != null)
				&& (this.commaSepUserNames.trim().length() > 0)) {
			sql = sql + " AND u.usename IN (" + this.commaSepUserNames + ")";
		}

		getGenericGrants(sql, "TABLE");
	}

	private void getFunctionGrants() throws SQLException {
		String funcNames = "";
		FunctionList list = this.schema.getFunctions();
		if (list.size() == 0) {
			return;
		}
		for (int i = 0; i < list.size(); i++) {
			if (i > 0) {
				funcNames = funcNames + ",";
			}
			funcNames = funcNames + "'" + list.get(i).getName() + "'";
		}

		String sql = " SELECT p.proacl, u.usename, p.proname, proargtypes, proallargtypes, n.nspname  FROM sr_proc p, sr_namespace n, sr_user u  WHERE n.nspname='"
				+ this.schema.getName()
				+ "' AND "
				+ "  \tp.pronamespace=n.oid AND p.proname IN ("
				+ funcNames
				+ ") AND"
				+ "   p.proowner=u.usesysid AND p.proacl IS NOT NULL ";

		if ((this.commaSepUserNames != null)
				&& (this.commaSepUserNames.trim().length() > 0)) {
			sql = sql + " AND u.usename IN (" + this.commaSepUserNames + ")";
		}

		getGenericGrants(sql, "FUNCTION");
	}

	private void getProcedureGrants() throws SQLException {
		String proNames = "";
		ProcedureList list = this.schema.getProcedures();
		if (list.size() == 0) {
			return;
		}
		for (int i = 0; i < list.size(); i++) {
			if (i > 0) {
				proNames = proNames + ",";
			}
			proNames = proNames + "'" + list.get(i).getName() + "'";
		}

		String sql = " SELECT p.proacl, u.usename, p.proname , proargtypes, proallargtypes, n.nspname  FROM sr_proc p, sr_namespace n, sr_user u  WHERE n.nspname='"
				+ this.schema.getName()
				+ "' AND "
				+ "  \tp.pronamespace=n.oid AND p.proname IN ("
				+ proNames
				+ ") AND"
				+ "   p.proowner=u.usesysid  AND p.proacl IS NOT NULL ";

		if ((this.commaSepUserNames != null)
				&& (this.commaSepUserNames.trim().length() > 0)) {
			sql = sql + " AND u.usename IN (" + this.commaSepUserNames + ")";
		}

		getGenericGrants(sql, "PROCEDURE");
	}

	private void getPackagesGrants() throws SQLException {
		String pkgNames = "";
		PackageList list = this.schema.getPackages();
		if (list.size() == 0) {
			return;
		}
		for (int i = 0; i < list.size(); i++) {
			if (i > 0) {
				pkgNames = pkgNames + ",";
			}
			pkgNames = pkgNames + "'" + list.get(i).getName() + "'";
		}

		String sql = " SELECT p.pkgacl, u.usename, p.pkgname, n.nspname  FROM edb_package p, sr_namespace n, sr_user u  WHERE n.nspname='"
				+ this.schema.getName()
				+ "' AND "
				+ "  \tp.pkgnamespace=n.oid AND p.pkgname IN ("
				+ pkgNames
				+ ") AND" + "   p.pkgowner=u.usesysid";

		if ((this.commaSepUserNames != null)
				&& (this.commaSepUserNames.trim().length() > 0)) {
			sql = sql + " AND u.usename IN (" + this.commaSepUserNames + ")";
		}

		getGenericGrants(sql, "PACKAGE");
	}

	private void getViewsGrants() throws SQLException {
		String viewNames = "";

		ViewList list = this.schema.getViews();
		if (list.size() == 0) {
			return;
		}
		for (int i = 0; i < list.size(); i++) {
			if (i > 0) {
				viewNames = viewNames + ",";
			}
			viewNames = viewNames + "'" + list.get(i).getName() + "'";
		}

		String sql = "SELECT c.relacl, u.usename, c.relname, n.nspname  FROM sr_catalog.sr_namespace n, sr_catalog.sr_class c, sr_catalog.sr_user u  WHERE c.relnamespace = n.oid  AND u.usesysid = c.relowner AND c.relkind = 'v'  AND n.nspname='"
				+ this.schema.getName()
				+ "' AND c.relname IN ("
				+ viewNames
				+ ") AND c.relacl IS NOT NULL ";

		if ((this.commaSepUserNames != null)
				&& (this.commaSepUserNames.trim().length() > 0)) {
			sql = sql + " AND u.usename IN (" + this.commaSepUserNames + ")";
		}

		getGenericGrants(sql, "");
	}

	private void getSequenceGrants() throws SQLException {
		String sequenceNames = "";

		SequenceList list = this.schema.getSequences();
		if (list.size() == 0) {
			return;
		}
		for (int i = 0; i < list.size(); i++) {
			if (i > 0) {
				sequenceNames = sequenceNames + ",";
			}
			sequenceNames = sequenceNames + "'" + list.get(i).getName() + "'";
		}

		String sql = "SELECT c.relacl, u.usename, c.relname,n.nspname  FROM sr_catalog.sr_namespace n, sr_catalog.sr_class c, sr_catalog.sr_user u  WHERE c.relnamespace = n.oid  AND u.usesysid = c.relowner AND c.relkind = 'S'  AND n.nspname='"
				+ this.schema.getName()
				+ "' AND c.relname IN ("
				+ sequenceNames + ") AND c.relacl IS NOT NULL ";

		if ((this.commaSepUserNames != null)
				&& (this.commaSepUserNames.trim().length() > 0)) {
			sql = sql + " AND u.usename IN (" + this.commaSepUserNames + ")";
		}

		getGenericGrants(sql, "SEQUENCE");
	}

	private void getGenericGrants(String query, String objectType)
			throws SQLException {
		Statement stmt = null;
		ResultSet rs = null;
		String schemaName = null;
		try {
			stmt = this.conn.getConnection().createStatement();
			rs = stmt.executeQuery(query);

			while (rs.next()) {
				String acl = rs.getString(1);
				String user = rs.getString(2);
				String obj = rs.getString(3);

				if ((objectType.equalsIgnoreCase("table"))
						|| (objectType.equalsIgnoreCase(""))
						|| (objectType.equalsIgnoreCase("sequence"))
						|| (objectType.equalsIgnoreCase("package"))
						|| (objectType.equalsIgnoreCase("procedure"))
						|| (objectType.equalsIgnoreCase("function"))) {
					schemaName = rs.getString("nspname");
				}

				if ((objectType.equalsIgnoreCase("Function"))
						|| (objectType.equalsIgnoreCase("Procedure"))) {
					String[] argTypes = rs.getString("proargtypes").split(
							"\\s+");
					if (rs.getString("proallargtypes") != null) {
						Long[] array = (Long[]) (Long[]) rs.getArray(
								"proallargtypes").getArray();
						argTypes = new String[array.length];
						for (int j = 0; j < argTypes.length; j++) {
							argTypes[j] = array[j].toString();
						}
					}
					obj = obj + "(";
					for (int j = 0; j < argTypes.length; j++) {
						if ((argTypes[j] == null)
								|| (argTypes[j].trim().length() == 0)) {
							continue;
						}
						if (j > 0) {
							obj = obj + ",";
						}
						obj = obj + getArgType(argTypes[j]);
					}
					obj = obj + ")";
				}

				Hashtable privs = parseACL(objectType, acl, user);
				Enumeration keys = privs.keys();
				while (keys.hasMoreElements()) {
					String key = (String) keys.nextElement();
					if (key.equalsIgnoreCase("unknown")) {
						continue;
					}
					Vector value = (Vector) privs.get(key);
					for (int i = 0; i < value.size(); i++) {
						String strGrantees = (String) value.elementAt(i);
						int index = strGrantees.indexOf("/");
						String grantee = strGrantees.substring(0, index);
						String grantor = strGrantees.substring(index + 1);
						grantor = grantor.equals("") ? user : grantor;
						boolean isPrivGrantable = value.elementAt(i)
								.equals("t");

						isPrivGrantable = (isPrivGrantable)
								|| (user.equals(grantee));

						boolean isAsterikGrant = false;

						if (new String(key).endsWith("*")) {
							key = key.substring(0, key.length() - 1);
							isPrivGrantable = true;
							isAsterikGrant = true;
						}

						if (key.equalsIgnoreCase("create temp")) {
							key = "TEMP";
						}

						Grant g = new Grant();
						g.setGrantable(isPrivGrantable);
						g.setGrantee(grantee);
						g.setGrantor(grantor);
						g.setPrivilege(key);
						g.setObjectName(objectType
								+ " "
								+ (schemaName == null ? ""
										: new StringBuilder()
												.append(schemaName).append(".")
												.toString()) + obj);
						g.setPrivilegeType(Grant.PRIV_TYPE.OBJECT);
						g.setOwner(user);

						this.schema.addGrant(g);
					}
				}
			}
		} finally {
			closeResultSet(rs);
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException sqle) {
			}
		}
	}

	private String getArgType(String oid) throws SQLException {
		PreparedStatement typeStmt = null;
		ResultSet rs = null;
		String type = "";
		try {
			typeStmt = this.conn
					.getConnection()
					.prepareStatement(
							"SELECT sr_catalog.format_type(sr_type.oid, typtypmod) AS type_name      FROM sr_catalog.sr_type      JOIN sr_catalog.sr_namespace ON (sr_namespace.oid = typnamespace)      WHERE sr_type.oid = ?");

			typeStmt.setInt(1, Integer.parseInt(oid));
			rs = typeStmt.executeQuery();
			if (rs.next())
				type = rs.getString(1);
		} finally {
			closeResultSet(rs);
			if (typeStmt != null) {
				typeStmt.close();
			}

		}

		return type;
	}

	protected void initRulesStatement(String commaSepRuleNames)
			throws SQLException {
		this.getRulesStr = "select schemaname, tablename, rulename, definition from sr_rules where schemaname=? ";
		if ((commaSepRuleNames != null)
				&& (commaSepRuleNames.trim().length() > 0)) {
			this.getRulesStr = (this.getRulesStr + "AND rulename IN ("
					+ commaSepRuleNames + ")");
		}

		this.getRules = this.conn.getConnection().prepareStatement(
				this.getRulesStr);
	}

	protected void getRules() throws SQLException {
		this.getRules.setString(1, this.schema.getName());
		ResultSet rs = this.getRules.executeQuery();
		try {
			while (rs.next()) {
				Rule r = new Rule(this.schema.getName(),
						rs.getString("rulename"));
				r.setTable(rs.getString("tablename"));
				r.setDefinition(rs.getString("definition"));
				this.schema.addRule(r);
			}
		} finally {
			closeResultSet(rs);
		}
	}

	protected void initGroupsStatement(String commaSepGroupNames)
			throws SQLException {
		this.getGroupsStr = "select groname, grolist from sr_group";
		if ((commaSepGroupNames != null)
				&& (commaSepGroupNames.trim().length() > 0)) {
			this.getGrantsStr = (this.getGrantsStr + " where groname IN ("
					+ commaSepGroupNames + ")");
		}

		this.getGrantsStr += " order by grosysid";
		this.getGroups = this.conn.getConnection().prepareStatement(
				this.getGroupsStr);

		this.getUserById = this.conn.getConnection().prepareStatement(
				"select sr_get_userbyid(?)");
	}

	protected void getGroups() throws SQLException {
		ResultSet rs = this.getGroups.executeQuery();
		ResultSet rs2 = null;
		try {
			while (rs.next()) {
				Group g = new Group(rs.getString("groname"));

				ArrayList members = new ArrayList();
				Long[] list = (Long[]) (Long[]) rs.getArray("grolist")
						.getArray();
				for (int i = 0; (list != null) && (i < list.length); i++) {
					this.getUserById.setLong(1, list[i].longValue());
					rs2 = this.getUserById.executeQuery();
					if (rs2.next()) {
						members.add(rs2.getString(1));
					}
					closeResultSet(rs2);
				}
				g.setMembers((String[]) (String[]) members
						.toArray(new String[0]));
				this.schema.addGroup(g);
			}
		} finally {
			closeResultSet(rs2);
			closeResultSet(rs);
		}
	}

	protected void initDomainsStatement(String commaSepDomainNames)
			throws SQLException {
		this.getDomainsStr = "select d.domain_name,data_type,domain_default  from information_schema.domains d  where domain_schema=?";

		if ((commaSepDomainNames != null)
				&& (commaSepDomainNames.trim().length() > 0)) {
			this.getDomainsStr = (this.getDomainsStr
					+ " AND d.domain_name IN (" + commaSepDomainNames + ")");
		}
		this.getDomains = this.conn.getConnection().prepareStatement(
				this.getDomainsStr);
		this.domainConstraints = this.conn
				.getConnection()
				.prepareStatement(
						"select c.domain_name,c.constraint_name,sr_get_constraintdef(pc.oid,true) as definition from information_schema.domain_constraints c,sr_constraint pc,sr_namespace nsp where c.domain_schema=? and c.domain_name=? AND pc.conname=c.constraint_name AND pc.connamespace=nsp.oid AND nsp.nspname=c.domain_schema");
	}

	protected void getDomains() throws SQLException {
		this.getDomains.setString(1, this.schema.getName());
		ResultSet rs = this.getDomains.executeQuery();
		ResultSet rs2 = null;
		try {
			while (rs.next()) {
				Domain d = new Domain(rs.getString("domain_name"));
				d.setDataType(rs.getString("data_type"));
				d.setDefaultValue(rs.getString("domain_default"));

				this.domainConstraints.setString(1, this.schema.getName());
				this.domainConstraints.setString(2, d.getName());
				rs2 = this.domainConstraints.executeQuery();
				while (rs2.next()) {
					String name = rs2.getString("constraint_name");
					String def = rs2.getString("definition");
					d.addConstraint(name, def);
				}
				closeResultSet(rs2);

				this.schema.addDomain(d);
			}
		} finally {
			closeResultSet(rs2);
			closeResultSet(rs);
		}
	}

	protected void closeResultSet(ResultSet rs) {
		try {
			if (rs != null)
				rs.close();
		} catch (Exception exp) {
		}
	}

	protected Hashtable parseACL(String objectType, String aclArray,
			String owner) {
		if ((aclArray == null) || (aclArray == "")) {
			if (objectType.equalsIgnoreCase("schema"))
				aclArray = "{" + owner + "=UC}";
			else if ((objectType.equalsIgnoreCase("table"))
					|| (objectType.equalsIgnoreCase(""))) {
				aclArray = "{" + owner + "=arwdRxt}";
			} else if (objectType.equalsIgnoreCase("sequence"))
				aclArray = "{" + owner + "=rwU}";
			else if ((objectType.equalsIgnoreCase("function"))
					|| (objectType.equalsIgnoreCase("package"))
					|| (objectType.equalsIgnoreCase("procedure"))) {
				aclArray = "{" + owner + "=X}";
			}
		}
		Vector acls = parseACLArray(aclArray);
		Hashtable privileges = new Hashtable();
		for (int i = 0; i < acls.size(); i++) {
			String acl = (String) acls.elementAt(i);
			addACLPrivileges(acl, privileges);
		}
		return privileges;
	}

	private void addACLPrivileges(String acl, Hashtable privileges) {
		if (acl.length() > 0) {
			int equalIndex = acl.lastIndexOf("=");
			int slIndex = acl.indexOf("/", equalIndex);
			String name = acl.substring(0, equalIndex);

			if (name.length() == 0) {
				name = "PUBLIC";
			}

			String privs = "";
			String grantor = "";

			if (slIndex != -1) {
				privs = acl.substring(equalIndex + 1, slIndex);
				grantor = acl.substring(slIndex + 1);
			} else {
				privs = acl.substring(equalIndex + 1);
				grantor = name;
			}

			for (int i = 0; i < privs.length(); i++) {
				char c = privs.charAt(i);
				String sqlpriv;
				switch (c) {
				case 'a':
					sqlpriv = "INSERT";
					break;
				case 'r':
					sqlpriv = "SELECT";
					break;
				case 'w':
					sqlpriv = "UPDATE";
					break;
				case 'd':
					sqlpriv = "DELETE";
					break;
				case 'R':
					sqlpriv = "RULE";
					break;
				case 'x':
					sqlpriv = "REFERENCES";
					break;
				case 't':
					sqlpriv = "TRIGGER";
					break;
				case 'X':
					sqlpriv = "EXECUTE";
					break;
				case 'U':
					sqlpriv = "USAGE";
					break;
				case 'C':
					sqlpriv = "CREATE";
					break;
				case 'T':
					sqlpriv = "CREATE TEMP";
					break;
				default:
					sqlpriv = "UNKNOWN";
				}

				if ((i + 1 < privs.length()) && (privs.charAt(i + 1) == '*')) {
					sqlpriv = sqlpriv + "*";
					i++;
				}

				Vector usersWithPermission = (Vector) privileges.get(sqlpriv);

				if (usersWithPermission == null) {
					usersWithPermission = new Vector();
					privileges.put(sqlpriv, usersWithPermission);
				}
				usersWithPermission.addElement(name + "/" + grantor);
			}
		}
	}

	private static Vector parseACLArray(String aclString) {
		Vector acls = new Vector();
		if ((aclString == null) || (aclString.length() == 0)) {
			return acls;
		}
		boolean inQuotes = false;

		int beginIndex = 1;
		char prevChar = ' ';
		for (int i = beginIndex; i < aclString.length(); i++) {
			char c = aclString.charAt(i);
			if ((c == '"') && (prevChar != '\\')) {
				inQuotes = !inQuotes;
			} else if ((c == ',') && (!inQuotes)) {
				acls.addElement(aclString.substring(beginIndex, i));
				beginIndex = i + 1;
			}

			prevChar = c;
		}

		acls.addElement(aclString.substring(beginIndex, aclString.length() - 1));

		for (int i = 0; i < acls.size(); i++) {
			String acl = (String) acls.elementAt(i);
			if ((acl.startsWith("\"")) && (acl.endsWith("\""))) {
				acl = acl.substring(1, acl.length() - 1);
			}
			acl = acl.replaceAll("\\\\\\\"", "\"").replaceAll("\"\"", "\"");
			if (!acl.equals(acls.elementAt(i))) {
				System.out.println(acl);
				acls.setElementAt(acl, i);
			}
		}
		return acls;
	}

	public String getDatabaseName() {
		if (this.databasename == null) {
			Statement st = null;
			ResultSet rs = null;
			try {
				st = this.dbConn.createStatement();
				rs = st.executeQuery("select current_database()");
				if (rs.next())
					this.databasename = rs.getString(1);
			} catch (Exception exp) {
			} finally {
				closeResultSet(rs);
				try {
					if (st != null)
						st.close();
				} catch (Exception ex) {
				}
			}
		}
		return this.databasename;
	}

	protected void initCustomDataTypesStatement(String commaSepTypes)
			throws SQLException {
		this.getCustomDataTypesStr = "SELECT t.oid, t.*, format_type(t.oid, null) AS alias, sr_get_userbyid(t.typowner)  as typeowner, e.typname as element, description, ct.oid AS taboid  FROM sr_type t LEFT OUTER JOIN sr_type e ON e.oid=t.typelem  LEFT OUTER JOIN sr_class ct ON ct.oid=t.typrelid AND ct.relkind <> 'c'  LEFT OUTER JOIN sr_description des ON des.objoid=t.oid  WHERE (t.typtype = 'c' OR t.typtype = 'e') AND t.typname NOT LIKE E'\\\\_%' AND ct.relkind IS NULL AND t.typnamespace = (select oid from sr_namespace where nspname =?) ORDER BY t.typname";

		this.getCustomDataTypes = this.conn.getConnection().prepareStatement(
				this.getCustomDataTypesStr);
		this.getDataTypeColumns = this.conn
				.getConnection()
				.prepareStatement(
						"SELECT attname, format_type(t.oid,att.atttypmod) AS typname,  attndims, atttypmod, nspname,  (SELECT COUNT(1) from sr_type t2 WHERE t2.typname=t.typname) > 1 AS isdup  FROM sr_attribute att  JOIN sr_type t ON t.oid=atttypid  JOIN sr_namespace nsp ON t.typnamespace=nsp.oid  LEFT OUTER JOIN sr_type b ON t.typelem=b.oid  WHERE att.attrelid=?  ORDER by attnum");

		this.getDataTypeLabels = this.conn
				.getConnection()
				.prepareStatement(
						" SELECT enumlabel  FROM sr_enum  WHERE enumtypid= ? ORDER by oid");
	}

	protected void getCustomDataTypes() throws SQLException {
		this.getCustomDataTypes.setString(1, this.schema.getName());
		ResultSet rs = this.getCustomDataTypes.executeQuery();
		ResultSet rs2 = null;
		try {
			while (rs.next()) {
				DataType type = new DataType(rs.getString("typname"));
				type.setOid(rs.getString("oid"));
				String typType = rs.getString("typtype");

				if (typType.equals(DataType.TypType.COMPOSITE.toString()))
					type.setTypType(DataType.TypType.COMPOSITE);
				else if (typType
						.equals(DataType.TypType.ENUMERATION.toString())) {
					type.setTypType(DataType.TypType.ENUMERATION);
				}

				type.setTypRelId(rs.getString("typrelid"));

				if (type.getTypType() == DataType.TypType.ENUMERATION) {
					this.getDataTypeLabels.setInt(1,
							Integer.parseInt(type.getOid()));
					rs2 = this.getDataTypeLabels.executeQuery();
					while (rs2.next()) {
						type.getTypeLabelsList()
								.add(rs2.getString("enumlabel"));
					}
					closeResultSet(rs2);
				} else if (type.getTypType() == DataType.TypType.COMPOSITE) {
					this.getDataTypeColumns.setInt(1,
							Integer.parseInt(type.getTypRelId()));
					rs2 = this.getDataTypeColumns.executeQuery();
					while (rs2.next()) {
						String attName = rs2.getString("attname");
						String attDataType = rs2.getString("typname");
						type.getTypeColumnsList().add(
								attName + " " + attDataType);
					}
					closeResultSet(rs2);
				}

				this.schema.addCustomType(type);
			}
		} finally {
			closeResultSet(rs2);
			closeResultSet(rs);
		}
	}

	@Override
	public void parseTables() throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	public String getParseTablesMsg() throws SQLException {
		return null;
	}
}

/*

 */